package cz.acies.uniql.dialect;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.Vector;

import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;


import cz.acies.uniql.connect.SQLConnect;
import cz.acies.uniql.metadata.FieldMetaData;
import cz.acies.uniql.metadata.IndexesMetaData;
import cz.acies.uniql.metadata.TableMetaData;
import cz.acies.uniql.sql.AlterTable;
import cz.acies.uniql.sql.SelectQuery;
import cz.acies.uniql.sql.UniQLExpr;
import cz.acies.uniql.sql.element.UniQLColumn;
import cz.acies.uniql.sql.element.UniQLCondition;
import cz.acies.uniql.sql.element.UniQLField;
import cz.acies.uniql.sql.element.UniQLGroupBy;
import cz.acies.uniql.sql.element.UniQLJoin;
import cz.acies.uniql.sql.element.UniQLLimit;
import cz.acies.uniql.sql.element.UniQLSubQuery;
import cz.acies.uniql.sql.functions.SQLAgregFunc;
import cz.acies.uniql.sql.functions.SQLFunc;
import cz.acies.utils.StringUtils;

public class OracleDialect extends Dialect
{
	// DBMetaData variables
	// TableMetaData variables
	// FieldMetadata variables
	// PrimaryKeyMetaData variables
	// IndexMetaData variables
	
	// Convert Element variables
	// Convert values variables

	public OracleDialect() {
		super();
		
		registerColumnType( Types.BIT, "number(1,0)" );
		registerColumnType( Types.BIGINT, "number(19,0)" );
		registerColumnType( Types.SMALLINT, "number(5,0)" );
		registerColumnType( Types.TINYINT, "number(3,0)" );
		registerColumnType( Types.INTEGER, "number(10,0)" );
		registerColumnType( Types.CHAR, "varchar2($l)" );
		registerColumnType( Types.VARCHAR, "varchar2($l)" );
		registerColumnType( Types.FLOAT, "number($p,$s)" );
		registerColumnType( Types.DOUBLE, "number($p,$s)" );
		registerColumnType( Types.REAL, "number($p,$s)" );
		registerColumnType( Types.DATE, "date" );
		registerColumnType( Types.TIME, "date" );
		registerColumnType( Types.TIMESTAMP, "timestamp" );
		registerColumnType( Types.VARBINARY, "blob" );
		registerColumnType( Types.NUMERIC, "number($p,$s)" );
		registerColumnType( Types.DECIMAL, "number($p,$s)" );
		registerColumnType( Types.BLOB, "blob" );
		registerColumnType( Types.CLOB, "varchar2(4000)" );
		//registerColumnType( Types.CLOB, "long varchar");
		registerColumnType( Types.LONGVARCHAR, "varchar2(4000)" );
		//registerColumnType( Types.LONGVARCHAR, "long varchar");

		// ----- register functions -------------------------------------------
		
		// Special functions 
		registerFunction("rownumber", "SQL_CALC_FOUND_ROWS");
		registerFunction("connection_id", "(SELECT SYS_CONTEXT ('USERENV', 'SESSION_USERID') FROM dual)");
		registerFunction("over", "");
		
		// Convert functions
		registerFunction("convert", "$1");
		registerFunction("text_to_varchar", "TO_CHAR($1)"); 					//CONVERT($1, VARCHAR(2048));
		registerFunction("to_varchar", "TO_CHAR($1)");
		registerFunction("to_number", "TO_NUMBER($1, '99999999999')");
		registerFunction("to_date", "TO_DATE($1, 'YYYY-MM-DD')");
		registerFunction("to_timestamp", "TO_TIMESTAMP($1, 'YYYY-MM-DD HH24:MI:SS')");
		registerFunction("date_to_varchar", "TO_CHAR($1)");
		
		// String functions
		registerFunction("left", "SUBSTR($1, 1, $2)");
		registerFunction("right", "SUBSTR($1, LENGTH($1)-$2, $2)");
		registerFunction("substring", "SUBSTR($1, $2, $3)");
		registerFunction("ltrim", "LTRIM($1)");
		registerFunction("rtrim", "RTRIM($1)");
		registerFunction("trim", "LTRIM(RTRIM($1))");
		registerFunction("lpad", "LPAD($1, $2, $3)");
		registerFunction("rpad", "RPAD($1, $2, $3)");
		registerFunction("length", "LENGTH($1)");
		registerFunction("char_length", "CHAR_LENGTH($1)");
		registerFunction("char", "CHR($1)");
		registerFunction("upper", "UPPER($1)");
		registerFunction("lower", "LOWER($1)");
	
		registerFunction("concat", "($n{ || })");
		registerFunction("password", "$1");
		registerFunction("locate", "INSTR($2, $1, $3)");
		registerFunction("replace", "REPLACE($1, $2, $3)");

		// Date & time functions
		registerFunction("now", "SYSTIMESTAMP");
		//registerFunction("dateadd", "(MINUTE($1) + INTERVAL ($2))");			//registerFunction("dateadd", "TIMESTAMPADD(SQL_TSI_DAY, $2, $3)");
		registerFunction("dateadd", "$3 + INTERVAL '$2' $1");					// CURRENT_DATE - INTERVAL '8' MONTH
		//registerFunction("add_date", "ADD_DATE($3 + INTERVAL ($2) $1)");
		registerFunction("date_add", "ADD_DATE($3 + INTERVAL ($2) $1)");
		registerFunction("date_format", "DATE_FORMAT($1, $2)");
		registerFunction("to_days", "TO_NUMBER(TO_CHAR($1, 'J'))");
		registerFunction("datediff_day", "TO_NUMBER(TO_CHAR($1, 'J')) - TO_NUMBER(TO_CHAR($2, 'J'))");
		
		// Logic functions
		registerFunction("if", "(CASE WHEN $1 THEN $2 ELSE $3 END)");
		registerFunction("isnull", "$1 IS NULL");
		registerFunction("not isnull", "$1 NOT IS NULL");
		registerFunction("ifnull", "NVL($1, $2)");
		registerFunction("if_isnull", "NVL($1, $2)");

		// Numericfunctions
		registerFunction("abs", "ABS($1)");
		registerFunction("round", "ROUND($1, $2)");

		// -----  -------------------------------------------------------------
		
		registerKeyword("ROW", "ROW_");
		registerKeyword("AUDIT", "AUDIT_MAIN");

		// DBMetaData variables
		// TableMetaData variables
		// FieldMetadata variables
		FMD_IDENTITY_BUILD_STYLE = 2;
		FMD_IDENTITY_BUILD_PART = "";
		//FMD_NULL_COLUMN_STRING = "NULL";
		
		// PrimaryKeyMetaData variables
		// IndexMetaData variables
		// Convert Element variables
		SQL_UPDATE_JOIN_WITH_FROM = true;
		SQL_DELETE_JOIN_WITH_FROM = true;
		
		// Convert values variables

	}

	// ----- Quoted methods ---------------------------------------------------
	
	public String LQ() { return "\""; }
	public String RQ() { return "\""; }
	public String CTI_LQ() { return ""; }
	public String CTI_RQ() { return ""; }

	// ----- Database connection methods --------------------------------------
		
	public String getSQLConnPrefix() {
		return "jdbc:oracle:thin:";
	}
	
	public String getSQLConnDriver() {
		return "oracle.jdbc.driver.OracleDriver";
	}
	
	/*
	public String genDBUrl(Properties prop) throws SQLException {
		String url = this.generateSQLConnUrl(prop);
		return url;	
	}
	*/
	
	public String generateSQLConnUrl(SQLConnect sqlconn) throws SQLException {
		
		//"jdbc:oracle:thin:@localhost:1521:XE"
		
		String prefix = sqlconn.getPrefix();
		String host = sqlconn.getHost();
		String port = sqlconn.getPort();//(String)prop.get("port");
		String database = sqlconn.getDatabase();
		
		sqlconn.getProperties().remove("host");
		sqlconn.getProperties().remove("port");
		sqlconn.getProperties().remove("database");
		
		String schema = (String)sqlconn.getProperty("schema");
		if (schema != null) {
			sqlconn.setSQLConnSchema(schema);
			this.setSchema(schema);
		}
		String url = prefix+"@"+host+":"+port+":"+database;
		return url;
	}
	
	public Properties parseURL(String url) {
		try {
			Properties prop = new Properties();
			
			String shl = "";
			String shr = "";
			
			int pos = url.indexOf("?");
			if (pos > -1) {
				shl = url.substring(0, pos);
				shr = url.substring(pos+1);
			} else {
				shl = url;
			}
			
			if (shl != null && shl.length() > 0) {
				
				pos = shl.indexOf("@");
				if (pos > -1) {
					String prefix = shl.substring(0, pos);
					shl = shl.substring(pos+1);
					prop.put("prefix", prefix);
				} 
				String host = shl;
				String as[] = host.split(":");
				prop.put("host", as[0]);
				if (as.length > 1) prop.put("port", as[1]);
				if (as.length > 1) prop.put("database", as[2]);
				//jdbc:oracle:thin:@localhost:1521:XE
				
			}
			if (shr != null && shr.length() > 0) {
				StringTokenizer st = new StringTokenizer(shr,"&");
				while (st.hasMoreTokens()) {
					String element = st.nextToken();
					pos = element.indexOf("=");
					if (pos > -1) {
						String key = element.substring(0, pos);
						String value = "";
						if (pos < element.length()) value = element.substring(pos+1, element.length());
						prop.put(key, value);
					}
				}
			}			
			return prop;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	// -----  -----------------------------------------------------------------
	
	/**
	 * Navraci defaultn� hodnotu Schema.
	 * 
	 * @return - vraci hodnotu Schema
	 */
	public String getDefaultSchema() {
		return "SYSTEM";
	}

	// -----  -----------------------------------------------------------------
	
	public synchronized Statement createStatement(Connection conn) throws SQLException {
		return conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 	
	}
	
	public synchronized Statement createStatement(Connection conn, int style) throws SQLException {
		int concur = ResultSet.CONCUR_READ_ONLY;
		switch (style) {
			case 0: concur = ResultSet.CONCUR_READ_ONLY; break;
			case 1: concur = ResultSet.CONCUR_UPDATABLE; break;
			default:
				break;
		}
		return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, concur); 	
	}

	// ----- ColumnType methods -----------------------------------------------
	
	// ----- ReservedKeywords methods -----------------------------------------
	
	// ----- Fn Methods -------------------------------------------------------
	
	//* ***********************************************************************
	//* Database Mapping methods
	//* ***********************************************************************
	
	// ----- DBMetaData methods -----------------------------------------------
	
	// ----- TableMetaData metdods --------------------------------------------
	
	// ----- FieldMetadata methods --------------------------------------------

	//public String getNullColumnString() { return "NULL"; }
	/*
	public int getIdentityColumnStyle() {
		return 0;
	}

	public String getIdentityColumnString() {
		return "";
	}
	*/
	// ----- IndexMetaData methods --------------------------------------------
	
	/**
	 * Funkce vygeneruje n�zev pro INDEX k tabilce.
	 *  
	 */		
	public String generateIndexName(String tableName, String indexName, String sufix) {
		String schema = (this.getSchema() != null) ? this.getSchema() : "";
		String name = schema+".IDX_"+tableName.toUpperCase()+"_"+sufix.toUpperCase();
		return name;
	}
	// ----- Convert Element methods ------------------------------------------
	
	public String getFromAsString() {
		return " ";
	}

	public String transferAlias(String alias) {
		return alias.toUpperCase();
	}

	/**
	public boolean isUpdateJoinWithFrom() {
		return true;
	}
	*/
	/*
	public boolean isDeleteJoinWithFrom() {
		return true;
	}
	*/
	public String tableNameToCase(String tableName) {
		return tableName.toUpperCase();
	}
	
	public String columnNameToCase(String tableName) {
		return tableName.toUpperCase();
	}
	
	// ----- Convert Values methods -------------------------------------------
	
	public String getValueTimestamp(String value) throws Exception {
		value = super.getValueTimestamp(value);
		return  "TO_TIMESTAMP("+value+", 'YYYY-MM-DD HH24:MI:SS')";
	}

	public String getValueDate(String value) {
		if (value.length() > 10) value = value.substring(0, 10);
		return  "TO_TIMESTAMP('"+value+"', 'YYYY-MM-DD')";	
	}

	public String getValueTime(String value) {
		return  "TO_TIMESTAMP('"+value+"', 'HH24:MI:SS')";	
	}

	public String getValueDouble(String value) {
		return "TO_NUMBER('"+value+"','99999999999.9999999')";
	}

	public String getValueInteger(String value) {
		return "TO_NUMBER('"+value+"','99999999999')";
	}

	public String getValueString(String value) {
		value = text2SQL(value);
		if (value.length() < 1) value = " ";
		return "'"+value+"'";
	}

	public String getValueString(String value, int length) {
		try {
			if (value == null || value.equals("null")) value = "";
			value = text2SQL(value);
			String s = new String(value.getBytes("UTF-8"));
			if (s.length() > length) {
				s = s.substring(0, length);
			}
			value = new String(s.getBytes(), "UTF-8");
			if (value.length() < 1) value = " ";
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "'"+value+"'";
	}

	public String getValueBlob(String value) {
		if (value.trim().length() == 0) value = "00";
		return "'"+value+"'";
	}

	public String getValueClob(String value) {
		if (value.length() < 1) value = " ";
		value = text2SQL(value);
		return "'"+value+"'";
	}

	/**
	 * 
	 */
	public String text2SQL(String input)  {
		StringBuffer buf = new StringBuffer("");
		for (int i = 0; i < input.length(); i++) {
			if (input.charAt(i) == '\\') {
				buf.append("\\\\");
			} else if (input.charAt(i) == '\'') {
				buf.append("\'\'");
			} else {
				buf.append(input.charAt(i));
			}
		} return buf.toString();
	}
	
	// ----- DDL methods ------------------------------------------------------
	
		
	// ----- DDL methods ------------------------------------------------------

	// ----- QueryRebuilder methods -------------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#generateLimitQuery(cz.acies.uniql.sql.SelectQuery)
	 */
	public synchronized String generateLimitQuery(SelectQuery query) throws Exception {
		//System.out.println("OracleDialect.generateLimitQuery( START )");
		UniQLLimit limit = query.getLimit();
		int first = limit.getFirstResult();
		int count = limit.getCountResult();
		
		String s = "SELECT * FROM (\n";
		s += "SELECT rnTo.*, rownum ROW_NUM FROM (\n";
		s += query.translate()+"\n";
		s += ") rnTo WHERE rownum <= ("+first+" + "+count+") \n";
		s += ") rnFrom WHERE ROW_NUM > "+first+" \n";
		//s += ") WHERE rownum BETWEEN "+first+" AND ("+first+" + "+count+")";
		//System.out.println("OracleDialect.generateLimitQuery( END )");
		return s;
	}
	
	/**
	 *	CREATE USER SMART4WEB_CCONNECT
  	 *		IDENTIFIED BY "smart"
     *		ACCOUNT UNLOCK;   
	 */
	public String getCreateDatabaseString() {
		return null;
	}
	
	/**
	 * 	CREATE TABLE SMART4WEB_ELCO."acc_day_book" (<br>
	 *		IDU VARCHAR2(10),<br>
	 *		HEADER VARCHAR2(200) DEFAULT '' NOT NULL,<br>
	 *		PRIMARY KEY (<br>
	 *			IDU<br>
	 *		) <br>
	 *	)<br>	
	 *  
	 * @see cz.acies.uniql.dialect.Dialect#getCreateTableString(com.smart4web.db.structure.TableMetaData)
	 * 
	 */
	/*
	public String getCreateTableString(TableMetaData tbmd) {
		String schema = (this.getSchema() != null) ? this.getSchema() : "" ;
		PrimaryKeyMetaData pkmd = tbmd.getPrimaryKey();
		
		String s = "CREATE TABLE \""+schema+"\".\""+tbmd.getName()+"\" (\n";
		for (int i = 0; i < tbmd.getFields().size(); i++) {
			FieldMetaData fmd = (FieldMetaData)tbmd.getFields().elementAt(i);
			s += fmd.toString();			
			if (i < tbmd.getFields().size()-1) {
				s += ",\n";
			} else {
				if (pkmd != null) s += ",\n"; 
				else s += "\n";
			}
		}		
		if (pkmd != null) {
			String columnname = pkmd.getColumn();
			s += "PRIMARY KEY ( \""+columnname+"\" ) ";
		}
		s += ")";
		
		return s;
	}
	*/
	/**
	 * 
	 */		
	public String getDropTableString(String schema,String table) {
		schema = this.getSchema();
		return "DROP TABLE \""+schema+"\".\""+table+"\"";
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getCreateTableFieldString(com.smart4web.db.structure.FieldMetaData)
	 */
	public String getCreateTableFieldString(FieldMetaData fmd) {
		String s = "";
		if (fmd.isPrefix()) s += this.LQ() + fmd.getTable() + this.RQ() + ".";
		s += (isKeyword(fmd.getName())) ? 
		    this.LQ()+(fmd.getName()+"_")+this.RQ() : 
		    this.LQ()+fmd.getName()+this.RQ();
		    
		fmd.setTypeName(this.getColumnTypeString(
		    fmd.getSqlType(), fmd.getSize(), fmd.getDecimal())
		);
		s += " " + fmd.getTypeName() + " ";

		if (fmd.isNullable()) {
			if (fmd.hasValue()) {
				s += "DEFAULT " + fmd.getValue() + " ";
			}
			s += this.getNullColumnString();
		} else {
			s += "DEFAULT " + fmd.getValue() + " ";
			s += " NOT NULL ";
		}
		return s;
	}
	
	/**
	 * 
	 */	
	public String getCreateIndexesString(IndexesMetaData ixmd) {
		Vector list = (Vector)getCreateIndexesList(ixmd);
		String s = "";
		for (Enumeration e = list.elements() ; e.hasMoreElements() ;) {
			String query = (String)e.nextElement();
			s += query + ((e.hasMoreElements()) ? "; " : "");
		}	 		
		return s;
	}

	/**
	 * 
	 */
	public Vector<String> getCreateIndexesList(IndexesMetaData ixmd) {
		Vector<String> list = new Vector<String>();
		//String schema = (ixmd.getSchema() != null) ? ixmd.getSchema() : "" ;
		String schema = this.getSchema();
		for (Enumeration e = ixmd.elements(); e.hasMoreElements();) {
			Hashtable params = (Hashtable)e.nextElement();
			Hashtable columns = (Hashtable)params.get("columns");
			
			String name = (String)params.get("INDEX_NAME");
			String tableName = ixmd.getTable();
			if (isKeyword(tableName.toUpperCase())) {
				tableName = getKeywords().getKeywordAlter(tableName.toUpperCase());
				ixmd.setTable(tableName);
			}
			//System.out.println("INDEX_NAME = "+name);	
			String query = "";
			if (name.equals("PRIMARY")) {
			} else {
				//String asc_desc = (String)params.get("ASC_OR_DESC");
				boolean nonunique = ((Boolean)params.get("NON_UNIQUE")).booleanValue();
				
				String colstr = "( ";
				for (int i = 0; i < columns.size(); i++) {
					String columnname = (String)columns.get(new Integer(i+1));
					//columnname = (ReservedKeywords.isReservedKeyword(columnname)) ? (columnname+"_") : columnname;
					colstr += LQ() + columnname + RQ(); 					
					if (i < columns.size()-1) colstr += ",";
				}
				colstr += " )";
								
				query = "CREATE ";
				if (!nonunique) { 
					query += "UNIQUE ";
				}
				// query += asc_desc;
				//query += "INDEX "+name+" ON "+schema+".\""+ixmd.getTable().toUpperCase()+"\" "+colstr;
				query += "INDEX "+name+" ON "+schema+".\""+ixmd.getTable().toUpperCase()+"\" "+colstr;
				list.addElement(query);
			}
		}
		return list;
	}
	
	/**
	 * 
	 */
	public String getDropIndexString(String tableName, String indexName) {
		return "DROP INDEX "+tableName+" ON ["+indexName+"]";
	}
	
	/**
	 * 
	 */
	public synchronized void execAlterTable(AlterTable alterTable, Connection conn) throws Exception {
		Statement stmt = null;		
		try {
			Dialect d = alterTable.getDialect();

			String schema = d.getSchema();
			String actionType = alterTable.getActionType();
			String table = alterTable.getTableName();
			
			TableMetaData tbmd = alterTable.getDBMetaData().getTable(table);
			FieldMetaData fmd = alterTable.fmd();

			String column = fmd.getName();
			String tableStr = d.LQ() + schema.toUpperCase() +d.RQ() + "."+d.LQ() + table.toUpperCase() + d.RQ();
			String columnStr = d.LQ() + column.toLowerCase() + d.RQ();
			String constrait = getColumnConstraintKey(table, column);

			String[] query = new String[0];
			if (actionType.equals(AlterTable.ADD_COLUMN)) {
				fmd.setTypeName(this.getColumnTypeString(fmd.getSqlType(), fmd.getSize(), fmd.getDecimal()));
				query = new String[1];
				query[0] = "ALTER TABLE " + tableStr + " " +
					"ADD (" + columnStr + " " + fmd.getTypeName() + " " + ((fmd.isNull()) ? " NULL" : " NOT NULL") + ")"; 
				//ALTER TABLE SMART4WEB_ASSM.ABOOK ADD (HEADER VARCHAR2(40) NOT NULL);
			} else if (actionType.equals(AlterTable.MODIFY_COLUMN)) {
				query = new String[1];
				fmd.setTypeName(this.getColumnTypeString(fmd.getSqlType(), fmd.getSize(), fmd.getDecimal()));
				query = new String[1];
				query[0] = "ALTER TABLE " + tableStr + " " +
					"MODIFY (" + columnStr + " " + fmd.getTypeName() + " " + ((fmd.isNull()) ? " NULL" : " NOT NULL") + ")";
				//ALTER TABLE SMART4WEB_ASSM.ABOOK MODIFY (FLD1 VARCHAR2(15));
			} else if (actionType.equals(AlterTable.DROP_COLUMN)) {
				query = new String[1];
				query[0] = "ALTER TABLE " + tableStr + " DROP COLUMN " + columnStr;
				//ALTER TABLE SMART4WEB_ASSM.ABOOK DROP COLUMN FLD1;
			}
			stmt = conn.createStatement();
			for (int i = 0; i < query.length; i++) {
				//System.out.println("OracleDialect.exec.query[i]:\n"+query[i]);
				if (query[i] != null) {
					try {
						stmt.execute(query[i]);	
					} catch (Exception exc) { 
						System.out.println("[ERROR] OracleDialect.execAlterTable(): "+exc.getMessage()+"\n\t"+query[i]);
					}						
				}
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			try { if (stmt != null) stmt.close(); } catch (SQLException e) {}
		} 
	}

	// ----- RowCount methods -------------------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#generateRowCountQuery(cz.acies.uniql.sql.SelectQuery)
	 */
	public synchronized SelectQuery generateRowCountQuery(SelectQuery query) throws Exception {
		try {
			SelectQuery sql = (SelectQuery)query.clone();
			HashMap aliasMap = query.getAliasMap();
			
			sql.removeOrderBy();
			ArrayList fields = sql.getFields();
			fields.clear();
			sql.removeLimit();
			sql.removeOrderBy();
			sql.removeGroupBy();
			UniQLField f = new UniQLField(new SQLFunc("COUNT").addParam("*")).as("ROW_COUNT");
			sql.addField(f);
			return sql;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return query;
	}

	// ----- GroubBy method ---------------------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#generateGroupByQuery(cz.acies.uniql.sql.SelectQuery)
	 */
	public synchronized SelectQuery generateGroupByQuery(SelectQuery query) throws Exception {
		//System.out.println("OracleDialect.generateGroupByQuery( START )");
		HashMap tables = new HashMap();
		String table = "";

		HashMap map = query.getAliasMap();
		UniQLGroupBy groupBy = query.getGroupBy();
		ArrayList list = groupBy.getExprList();
		
		ArrayList fields = query.getFields();
//$		
		UniQLSubQuery subquery = new UniQLSubQuery(this, null); 
				
		String fromName = (String)map.get("mainFrom");
		HashMap fromIndex = (HashMap)map.get("fromIndex");
		HashMap joinIndex = (HashMap)map.get("joinIndex");
		if (fromIndex == null) {
			fromIndex = new HashMap();
			map.put("fromIndex", fromIndex);
		}
		if (joinIndex == null) {
			joinIndex = new HashMap();
			map.put("joinIndex", joinIndex);
		}		
		UniQLExpr expr = null;
		int grpcount = list.size();
		
		if (fromIndex.containsKey("group_alias")) {
			fromIndex.remove("group_alias");
		}
		for (int i = grpcount-1; i > -1; i--) {
			Object obj = list.get(i);
			if (obj instanceof UniQLColumn) {
				UniQLColumn column = (UniQLColumn)obj;
				table = column.getTableName();
				if (table != null && table.length() > 0) {
					//System.out.println("MSSQLDialect.groupBy().table = "+table+", fromName = "+fromName);
					table = table.toLowerCase(); 
					if (table.equals(fromName)) {
						//System.out.println("MSSQLDialect.groupBy().fromIndex = " + fromIndex); 
						if (fromIndex.containsKey(table)) {
							expr = (UniQLExpr)fromIndex.get(table);
						} else {
							expr = (UniQLExpr)((ArrayList)query.getTables()).get(0);
						}						
						if (expr != null) {
							String repl = (String)map.get(table);
							
							subquery.getSelect().addField(column);
							if (subquery.getSelect().getTables().isEmpty()) {
								subquery.getSelect().addFrom((repl != null) ? repl : table);
							} else {
								((HashMap)subquery.getSelect().getAliasMap().get("fromIndex")).get("");
							}
							subquery.getSelect().addGroupBy(column);
							if (!subquery.getSelect().getAliasMap().containsKey("GROUP_UID")) {
								subquery.getSelect().addField(
								    new UniQLField(
								 		new SQLFunc("MAX").addParam(new UniQLColumn(table+".IDU"))
								 	).as("GROUP_UID")
								);
							}
							int fldcnt = fields.size();
							for (int j = fldcnt-1; j > -1; j--) {
								UniQLExpr field = ((UniQLExpr)fields.get(j));
								if (field instanceof UniQLField) {
									UniQLExpr fn = ((UniQLField)field).getExpr();
									if (fn instanceof SQLAgregFunc) {
										subquery.getSelect().addField(field);
										query.removeField(j);
									}
								} else if (field instanceof UniQLColumn) {									
									
								}
							}
							String xml = subquery.transToXML();
							xml = StringUtils.replace(xml, table, repl);
							try {
								Document doc = DocumentHelper.parseText(xml);
								Element node = doc.getRootElement();
								subquery = (UniQLSubQuery)subquery.xmlToExpr(node);
							} catch (Exception e) {
								System.out.println(e.getMessage());
							} 
							subquery.as("group_alias");

							query.removeFrom(0);
							query.addFrom(subquery);
							query.getGroupBy().removeExpr(column);
							
							if (!fromIndex.containsKey("group_alias")) {
								UniQLJoin join = new UniQLJoin()
									.addFrom(expr)
									.addCondition(new UniQLCondition("group_alias.GROUP_UID", "=", table+".IDU"));
								
								query.addJoin(0, join);
								query.addField("group_alias.*");
								joinIndex.put(table, join);
								fromIndex.put("group_alias", subquery);							
							}
						}
					} else {
						if (joinIndex.containsKey(table)) {
							String repl = (String)map.get(table);
							subquery.getSelect().addField(column);
							subquery.getSelect().addFrom((repl != null) ? repl : table);
							subquery.getSelect().addGroupBy(column);
							expr = (UniQLExpr)joinIndex.get(table);
							
							fields = query.getFields();							
							int fldcnt = fields.size();
							for (int j = fldcnt-1; j > -1; j--) {
								UniQLExpr field = ((UniQLExpr)fields.get(j));
								if (field instanceof UniQLField) {
									UniQLExpr fn = ((UniQLField)field).getExpr();
									if (fn instanceof SQLAgregFunc) {
										subquery.getSelect().addField(field);
										query.removeField(j);
									}
								} else if (field instanceof UniQLColumn) {									
									UniQLColumn fldColumn = (UniQLColumn)field;
									String fldAlias = fldColumn.getAlias(); 
									String tabName = fldColumn.getTableName();
									String colName = fldColumn.getName();
									if ((fldAlias != null && fldAlias.equals(table)) || (tabName != null && tabName.equals(table))) {
										fldColumn.setAlias(null); 
										SQLAgregFunc fn = new SQLAgregFunc("MAX");
										fn.addParam(fldColumn);
										System.out.println("MSSQLDialect.generateGroupByQuery()");
										subquery.getSelect().addField(fn, (fldAlias != null) ? fldAlias : colName);
										query.removeField(j);
									}
								}
								UniQLJoin join = (UniQLJoin)expr;
								join.addFrom(subquery);
								query.getGroupBy().removeExpr(column);
							}
							String xml = subquery.transToXML();
							xml = StringUtils.replace(xml, table, repl);
							try {
								Document doc = DocumentHelper.parseText(xml);
								Element node = doc.getRootElement();
								subquery = (UniQLSubQuery)subquery.xmlToExpr(node);
							} catch (Exception e) {
								System.out.println(e.getMessage());
							} 
							subquery.as(table);
						}
					}
				} else {
					//table = fromName;
					String colName = column.getName();
				}
			} else if (obj instanceof SQLFunc) {
				//System.out.println("MSSQLDialect.groupBy(10)");
				SQLFunc column = (SQLFunc)obj;
				table = fromName;
				if (table.equals(fromName)) {
					//System.out.println("MSSQLDialect.groupBy().fromIndex = " + fromIndex); 
					if (fromIndex.containsKey(table)) {
						expr = (UniQLExpr)fromIndex.get(table);
					} else {
						expr = (UniQLExpr)((ArrayList)query.getTables()).get(0);
					}						
					if (expr != null) {
						String repl = (String)map.get(table);
						//subquery.getSelect().addField(column);
						if (subquery.getSelect().getTables().isEmpty()) {
							subquery.getSelect().addFrom((repl != null) ? repl : table);
						} else {
							((HashMap)subquery.getSelect().getAliasMap().get("fromIndex")).get("");
						}
						column.setDialect(this);
						//System.out.println("MSSQLDialect.groupBy(11).fn = "+column.translate());
						subquery.getSelect().addGroupBy(column);
						if (!subquery.getSelect().getAliasMap().containsKey("GROUP_UID")) {
							subquery.getSelect().addField(
							    new UniQLField(
							 		new SQLFunc("MAX").addParam(new UniQLColumn(table+".IDU"))
							 	).as("GROUP_UID")
							);
						}
						int fldcnt = fields.size();
						for (int j = fldcnt-1; j > -1; j--) {
							UniQLExpr field = ((UniQLExpr)fields.get(j));
							if (field instanceof UniQLField) {
								UniQLExpr fn = ((UniQLField)field).getExpr();
								if (fn instanceof SQLAgregFunc) {
									subquery.getSelect().addField(field);
									query.removeField(j);
								}
							} else if (field instanceof UniQLColumn) {									
								
							}
						}
						String xml = subquery.transToXML();
						xml = StringUtils.replace(xml, table, repl);
						try {
							//System.out.println("MSSQLDialect.groupBy(12).xml = "+xml);
							Document doc = DocumentHelper.parseText(xml);
							Element node = doc.getRootElement();
							//System.out.println("MSSQLDialect.groupBy(12).xml = "+node.asXML());
							subquery = (UniQLSubQuery)subquery.xmlToExpr(node);
						} catch (Exception e) {
							System.out.println(e.getMessage());
						} 
						subquery.as("group_alias");
	
						query.removeFrom(0);
						query.addFrom(subquery);
						query.getGroupBy().removeExpr(column);
						
						if (!fromIndex.containsKey("group_alias")) {
							UniQLJoin join = new UniQLJoin()
								.addFrom(expr)
								.addCondition(new UniQLCondition("group_alias.GROUP_UID", "=", table+".IDU"));
							
							query.addJoin(0, join);
							query.addField("group_alias.*");
							joinIndex.put(table, join);
							fromIndex.put("group_alias", subquery);							
						}
					}
				}
			}
			//System.out.println("MSSQLDialect.generateGroupByQuery().table = "+table);
		}
		query.removeGroupBy();
		//System.out.println("OracleDialect.generateGroupByQuery( END )");
		return query;
	}
	
	public boolean isGroupByRewrite() {
		return true;
	} 
	
	// ----- LOCK & UNLOCK TABLE methtods -------------------------------------
	
	public String lockTable(String table) {
		String sql = "";
		//String sql = "LOCK TABLES "+table+";";
		return sql;
		
	}
	
	public String unlockTable(String table) {
		String sql = "";
		//String sql = "UNLOCK TABLES;"; 
		return sql;
	}
}
